﻿IF OBJECT_ID ('CHO_TASKS') IS NOT NULL
	DROP TABLE CHO_TASKS
GO

CREATE TABLE CHO_TASKS
	(
	TASK_ID				INT NOT NULL,
	TASK_NAME			VARCHAR (50) NOT NULL UNIQUE,
	WORKFLOW_NAME		VARCHAR (50) NOT NULL,
	TASK_DESC			VARCHAR (2500),
	TASK_GRP_ID			VARCHAR (50) NOT NULL,
	CMD_PARAMETERS		VARCHAR(2500),
	NO_OF_RETRY			INT,
	SLEEP_BETWEEN_RETRY INT,
	[TIMEOUT]			INT,
	DELETED	  			BIT NOT NULL,
	MACHINE_NAME		VARCHAR(50) NOT NULL,
	CREATED_BY			VARCHAR(100) NOT NULL,
	CREATED_DATE		DATETIME NOT NULL,
	MODIFIED_BY			VARCHAR(100) NOT NULL,
	MODIFIED_DATE		DATETIME NOT NULL,
	CONSTRAINT PK_CHO_TASKS PRIMARY KEY (TASK_ID)
	)
GO

CREATE INDEX IX_CHO_TASKS
	ON CHO_TASKS (TASK_NAME)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_TASKS_AUDIT') IS NOT NULL
	DROP TABLE CHO_TASKS_AUDIT
GO

CREATE TABLE CHO_TASKS_AUDIT
	(
	TASK_ID				INT NOT NULL,
	TASK_NAME			VARCHAR (50) NOT NULL,
	WORKFLOW_NAME		VARCHAR (50) NOT NULL,
	TASK_DESC			VARCHAR (2500),
	TASK_GRP_ID			VARCHAR (50) NOT NULL,
	CMD_PARAMETERS		VARCHAR(2500),
	NO_OF_RETRY			INT,
	SLEEP_BETWEEN_RETRY INT,
	[TIMEOUT]			INT,
	DELETED	      		BIT NOT NULL,
	MACHINE_NAME		VARCHAR(50) NOT NULL,
	CREATED_BY			VARCHAR(100) NOT NULL,
	CREATED_DATE		DATETIME NOT NULL,
	MODIFIED_BY			VARCHAR(100) NOT NULL,
	MODIFIED_DATE		DATETIME NOT NULL,
	CONSTRAINT PK_CHO_TASKS_AUDIT PRIMARY KEY (TASK_ID, MODIFIED_DATE)
	)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_ADD_OR_UPDATE_TASK') IS NOT NULL
	DROP PROC CHO_ADD_OR_UPDATE_TASK
GO

CREATE PROCEDURE CHO_ADD_OR_UPDATE_TASK
		@TASK_NAME VARCHAR(50),
		@WORKFLOW_NAME VARCHAR (50),
		@TASK_DESC VARCHAR(2500),
		@TASK_GRP_ID INT,
		@CMD_PARAMETERS	VARCHAR(2500),
		@NO_OF_RETRY INT,
		@SLEEP_BETWEEN_RETRY INT,
		@TIMEOUT INT,
		@MACHINE_NAME VARCHAR(50),
		@USER_NAME VARCHAR(100),
		@TASK_ID INT OUTPUT
		AS

	SET NOCOUNT ON

	SELECT @TASK_ID = NULL

	DECLARE @DATE_UPDATED DATETIME
	DECLARE @DELETED BIT
	DECLARE @ERR_MESSAGE VARCHAR(255)
	SELECT @TASK_ID = NULL
	--DECLARE @TASK_GRP_ID INT

	--SELECT @TASK_GRP_ID = TASK_GRP_ID FROM CHO_TASK_GRPS WHERE TASK_GRP_NAME = @TASK_GRP_NAME AND DELETED = 0

	IF NOT EXISTS(SELECT 1 FROM CHO_TASK_GRPS WHERE TASK_GRP_ID = @TASK_GRP_ID AND DELETED = 0)
	BEGIN
		SET @ERR_MESSAGE = CAST(@TASK_GRP_ID as VARCHAR(25)) + ' task group not exists/deleted.'
		RAISERROR (@ERR_MESSAGE, 11,1)
		RETURN
	END	
	
	SELECT @TASK_ID = TASK_ID, @DELETED = DELETED FROM CHO_TASKS WHERE TASK_NAME = @TASK_NAME
	SELECT @DATE_UPDATED = GETDATE()
	
	IF @TASK_ID IS NULL
	BEGIN
		INSERT CHO_TASKS (TASK_NAME, WORKFLOW_NAME, TASK_DESC, TASK_GRP_ID, CMD_PARAMETERS, NO_OF_RETRY, SLEEP_BETWEEN_RETRY,
			[TIMEOUT], DELETED, MACHINE_NAME, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
		VALUES (@TASK_NAME, @WORKFLOW_NAME, @TASK_DESC, @TASK_GRP_ID, @CMD_PARAMETERS, @NO_OF_RETRY, @SLEEP_BETWEEN_RETRY, 
			@TIMEOUT, 0, @MACHINE_NAME, @USER_NAME, @DATE_UPDATED, @USER_NAME, @DATE_UPDATED)
		SELECT @TASK_ID = @@IDENTITY
		
		INSERT INTO CHO_TASKS_AUDIT 
			SELECT * FROM CHO_TASKS
			WHERE
				TASK_ID = @TASK_ID
	END
	ELSE
	BEGIN
		IF (@DELETED = 0)
		BEGIN
			UPDATE CHO_TASKS
				SET 
					WORKFLOW_NAME = @WORKFLOW_NAME,
					TASK_DESC = @TASK_DESC,
					CMD_PARAMETERS = @CMD_PARAMETERS,
					NO_OF_RETRY = @NO_OF_RETRY,
					SLEEP_BETWEEN_RETRY = @SLEEP_BETWEEN_RETRY,
					[TIMEOUT] = @TIMEOUT,
					MACHINE_NAME = @MACHINE_NAME,
					MODIFIED_BY = @USER_NAME,
					MODIFIED_DATE = @DATE_UPDATED
				WHERE
					TASK_ID = @TASK_ID

			INSERT INTO CHO_TASKS_AUDIT 
				SELECT * FROM CHO_TASKS
				WHERE
					TASK_ID = @TASK_ID
		END
		ELSE
		BEGIN
			SET @ERR_MESSAGE = @TASK_NAME + ' task is in deleted state.'
			RAISERROR (@ERR_MESSAGE, 11,1)
			RETURN
		END
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_DELETE_TASK') IS NOT NULL
	DROP PROC CHO_DELETE_TASK
GO

CREATE PROCEDURE CHO_DELETE_TASK
		@TASK_ID INT,
		@USER_NAME VARCHAR(100)
		AS

	SET NOCOUNT ON

	IF EXISTS(SELECT 1 FROM CHO_TASKS WHERE TASK_ID = @TASK_ID AND DELETED = 0)
	BEGIN
		EXEC CHO_DELETE_ALL_TASK_SCHEDULES @TASK_ID, @USER_NAME

		UPDATE CHO_TASKS SET DELETED = 1, MODIFIED_BY = @USER_NAME, MODIFIED_DATE = GETDATE()
			WHERE TASK_ID = @TASK_ID

		INSERT INTO CHO_TASKS_AUDIT 
			SELECT * FROM CHO_TASKS
			WHERE
				TASK_ID = @TASK_ID
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_MOVE_TASK') IS NOT NULL
	DROP PROC CHO_MOVE_TASK
GO

CREATE PROCEDURE CHO_MOVE_TASK
		@TASK_NAME VARCHAR(50),
		@SRC_TASK_GRP_ID INT,
		@DEST_TASK_GRP_ID INT,
		@USER_NAME VARCHAR(100)
		AS

	SET NOCOUNT ON

	IF EXISTS(SELECT 1 FROM CHO_TASKS WHERE TASK_NAME = @TASK_NAME AND DELETED = 0 AND TASK_GRP_ID = @SRC_TASK_GRP_ID)
	BEGIN
		UPDATE CHO_TASKS SET TASK_GRP_ID = @DEST_TASK_GRP_ID, MODIFIED_BY = @USER_NAME, MODIFIED_DATE = GETDATE()
		WHERE
			TASK_NAME = @TASK_NAME
			AND TASK_GRP_ID = @SRC_TASK_GRP_ID
			AND DELETED = 0
	
		INSERT INTO CHO_TASKS_AUDIT 
			SELECT * FROM CHO_TASKS
			WHERE
				TASK_NAME = @TASK_NAME
	END
GO

----------------------------------------------------------------


IF OBJECT_ID ('CHO_SELECT_TASKS') IS NOT NULL
	DROP PROC CHO_SELECT_TASKS
GO

CREATE PROCEDURE CHO_SELECT_TASKS_UI
	@TASK_NAME VARCHAR(50),
	@TASK_GRP_NAME VARCHAR(50),
	@MACHINE_NAME VARCHAR(50),
	@MODIFIED_DATE DATETIME
AS

	SET NOCOUNT ON
	
	IF (@MODIFIED_DATE is NULL)
	BEGIN
		SELECT @MODIFIED_DATE = '1753-01-01'
	END

	IF (@TASK_NAME is NULL AND @TASK_GRP_NAME is NULL)
	BEGIN
		SELECT 
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			TS.SCHEDULE_NAME,
			ISNULL(B.BATCH_ID, 0) as BATCH_ID,
			ISNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			ISNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			T.CMD_PARAMETERS,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID,
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MODIFIED_BY,
			ISNULL(B.MODIFIED_DATE, '1/1/2013') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
			CHO_TASKS T
			LEFT OUTER JOIN CHO_TASK_GRPS G ON T.TASK_GRP_ID = G.TASK_GRP_ID AND G.DELETED = 0
			LEFT OUTER JOIN CHO_TASK_SCHEDULES TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN CHO_TASK_BATCH B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
				--AND B.[VERSION] = (SELECT MAX(X.[VERSION]) FROM CHO_TASK_BATCH X WHERE X.TASK_ID = T.TASK_ID)
				AND B.MODIFIED_DATE >= @MODIFIED_DATE
			LEFT OUTER JOIN CHO_TASK_STATUS S ON B.TASK_STATUS = S.TASK_STATUS_ID
		WHERE 
			T.DELETED = 0
			AND T.MACHINE_NAME LIKE ISNULL(@MACHINE_NAME, '%')
			AND B.MODIFIED_DATE >= @MODIFIED_DATE
	END
	ELSE IF @TASK_GRP_NAME is NULL
	BEGIN
		SELECT 
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			TS.SCHEDULE_NAME,
			ISNULL(B.BATCH_ID, 0) as BATCH_ID,
			ISNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			ISNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			T.CMD_PARAMETERS,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID, 
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MODIFIED_BY,
			ISNULL(B.MODIFIED_DATE, '1/1/2013') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
			CHO_TASKS T
			LEFT OUTER JOIN CHO_TASK_GRPS G ON T.TASK_GRP_ID = G.TASK_GRP_ID AND G.DELETED = 0
			LEFT OUTER JOIN CHO_TASK_SCHEDULES TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN CHO_TASK_BATCH B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
				--AND B.[VERSION] = (SELECT MAX(X.[VERSION]) FROM CHO_TASK_BATCH X WHERE X.TASK_ID = T.TASK_ID)
				AND B.MODIFIED_DATE >= @MODIFIED_DATE
			LEFT OUTER JOIN CHO_TASK_STATUS S ON B.TASK_STATUS = S.TASK_STATUS_ID
		WHERE 
			T.TASK_NAME = @TASK_NAME
			AND T.DELETED = 0
			AND T.MACHINE_NAME LIKE ISNULL(@MACHINE_NAME, '%')
			AND B.MODIFIED_DATE >= @MODIFIED_DATE
	END
	ELSE IF (@TASK_NAME is NULL)
	BEGIN
		SELECT 
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			TS.SCHEDULE_NAME,
			ISNULL(B.BATCH_ID, 0) as BATCH_ID,
			ISNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			ISNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			T.CMD_PARAMETERS,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID, 
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MODIFIED_BY,
			ISNULL(B.MODIFIED_DATE, '1/1/2013') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
			CHO_TASKS T
			LEFT OUTER JOIN CHO_TASK_GRPS G ON T.TASK_GRP_ID = G.TASK_GRP_ID AND G.DELETED = 0
			LEFT OUTER JOIN CHO_TASK_SCHEDULES TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN CHO_TASK_BATCH B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
				--AND B.[VERSION] = (SELECT MAX(X.[VERSION]) FROM CHO_TASK_BATCH X WHERE X.TASK_ID = T.TASK_ID)
				AND B.MODIFIED_DATE >= @MODIFIED_DATE
			LEFT OUTER JOIN CHO_TASK_STATUS S ON B.TASK_STATUS = S.TASK_STATUS_ID
		WHERE 
			G.TASK_GRP_NAME = @TASK_GRP_NAME
			AND T.DELETED = 0
			AND T.MACHINE_NAME LIKE ISNULL(@MACHINE_NAME, '%')
			AND B.MODIFIED_DATE >= @MODIFIED_DATE
	END
	ELSE
	BEGIN
		SELECT 
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			TS.SCHEDULE_NAME,
			ISNULL(B.BATCH_ID, 0) as BATCH_ID,
			ISNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			ISNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID, 
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MODIFIED_BY,
			ISNULL(B.MODIFIED_DATE, '1/1/2013') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
			CHO_TASKS T
			LEFT OUTER JOIN CHO_TASK_GRPS G ON T.TASK_GRP_ID = G.TASK_GRP_ID AND G.DELETED = 0
			LEFT OUTER JOIN CHO_TASK_SCHEDULES TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN CHO_TASK_BATCH B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
				--AND B.[VERSION] = (SELECT MAX(X.[VERSION]) FROM CHO_TASK_BATCH X WHERE X.TASK_ID = T.TASK_ID)
				AND B.MODIFIED_DATE >= @MODIFIED_DATE
			LEFT OUTER JOIN CHO_TASK_STATUS S ON B.TASK_STATUS = S.TASK_STATUS_ID
		WHERE 
			T.TASK_NAME = @TASK_NAME
			AND G.TASK_GRP_NAME = @TASK_GRP_NAME
			AND T.DELETED = 0
			AND T.MACHINE_NAME LIKE ISNULL(@MACHINE_NAME, '%')
			AND B.MODIFIED_DATE >= @MODIFIED_DATE
	END
GO


----------------------------------------------------------------

IF OBJECT_ID ('CHO_SELECT_DEPENDENCY_TASKS') IS NOT NULL
	DROP PROC CHO_SELECT_DEPENDENCY_TASKS
GO

CREATE PROCEDURE CHO_SELECT_DEPENDENCY_TASKS
	@JOB_NAME VARCHAR(50)
AS
	SELECT
		T.TASK_ID,
		T.TASK_NAME,
		T.WORKFLOW_NAME,
		TS.SCHEDULE_ID,
		TS.SCHEDULE_NAME,
		TS.DEPENDENCIES,
		TS.FIRE_AFTER_DEPENDENCY,
		B.TASK_STATUS,
		B.START_TIME
	FROM
		CHO_TASKS T
		LEFT OUTER JOIN CHO_TASK_BATCH B ON T.TASK_ID = B.TASK_ID 
			AND B.[VERSION] = (SELECT MAX(X.[VERSION]) FROM CHO_TASK_BATCH X WHERE X.TASK_ID = T.TASK_ID)
		LEFT OUTER JOIN CHO_TASK_SCHEDULES TS ON T.TASK_ID = TS.TASK_ID
	WHERE 
		T.DELETED = 0
		AND TS.DEPENDENCIES LIKE '%(' + @JOB_NAME + ')%'
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_IS_TASK_EXISTS') IS NOT NULL
	DROP PROC CHO_IS_TASK_EXISTS
GO

CREATE PROCEDURE CHO_IS_TASK_EXISTS
		@TASK_NAME VARCHAR(50),
		@DELETED BIT OUTPUT,
		@TASK_ID INT OUTPUT
		AS

	SET NOCOUNT ON

	SELECT @DELETED = 0
	SELECT @TASK_ID = NULL

	SELECT @TASK_ID = TASK_ID, @DELETED = DELETED FROM CHO_TASKS WHERE TASK_NAME = @TASK_NAME
	IF @TASK_ID is NULL
	BEGIN
		SELECT @TASK_ID = 0
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_UNDELETE_TASK') IS NOT NULL
	DROP PROC CHO_UNDELETE_TASK
GO

CREATE PROCEDURE CHO_UNDELETE_TASK
		@TASK_NAME VARCHAR(50),
		@USER_NAME VARCHAR(100)
		AS

	SET NOCOUNT ON

	DECLARE @TASK_ID INT

	SELECT @TASK_ID = TASK_ID FROM CHO_TASKS WHERE TASK_NAME = @TASK_NAME AND DELETED = 1
	IF @TASK_ID is NULL
	BEGIN
		RETURN
	END
	ELSE
	BEGIN
		UPDATE CHO_TASKS SET DELETED = 0, MODIFIED_BY = @USER_NAME, MODIFIED_DATE = GETDATE() WHERE TASK_NAME = @TASK_NAME

		INSERT INTO CHO_TASKS_AUDIT 
			SELECT * FROM CHO_TASKS
			WHERE
				TASK_NAME = @TASK_NAME
	END
GO


----------------------------------------------------------------

IF OBJECT_ID ('CHO_CLONE_TASK_DEFINITION') IS NOT NULL
	DROP PROC CHO_CLONE_TASK_DEFINITION
GO

CREATE PROCEDURE CHO_CLONE_TASK_DEFINITION
		@TASK_NAME VARCHAR(50),
		@NEW_TASK_NAME VARCHAR(50),
		@NEW_TASK_GRP_NAME VARCHAR(50),
		@USER_NAME VARCHAR(100),
		@NEW_TASK_ID INT OUTPUT
		AS

	SET NOCOUNT ON

	SELECT @NEW_TASK_ID = NULL
	DECLARE @ERR_MESSAGE VARCHAR(255)

	DECLARE @TASK_ID INT
	DECLARE @WORKFLOW_NAME VARCHAR(50)
	DECLARE @DELETED BIT
	DECLARE @TASK_DESC VARCHAR(2500)
	DECLARE @TASK_GRP_ID INT
	DECLARE @CMD_PARAMETERS VARCHAR(2500)
	DECLARE @NO_OF_RETRY INT
	DECLARE @SLEEP_BETWEEN_RETRY INT
	DECLARE @TIMEOUT INT
	DECLARE @MACHINE_NAME VARCHAR(50)
	DECLARE @NEW_TASK_GRP_ID INT

	IF EXISTS (SELECT 1 FROM CHO_TASKS WHERE TASK_NAME = @NEW_TASK_NAME)
	BEGIN
		SET @ERR_MESSAGE = 'Task ''' + @NEW_TASK_NAME + ''' already exists.'
		RAISERROR (@ERR_MESSAGE, 11,1)
		RETURN
	END

	IF NOT (@NEW_TASK_GRP_NAME IS NULL)
	BEGIN
		SELECT @NEW_TASK_GRP_ID = TASK_GRP_ID FROM CHO_TASK_GRPS WHERE TASK_GRP_NAME = @NEW_TASK_GRP_NAME
		IF (@NEW_TASK_GRP_ID is NULL)
		BEGIN
			SET @ERR_MESSAGE = 'Task Group ''' + @NEW_TASK_GRP_NAME + ''' not exists.'
			RAISERROR (@ERR_MESSAGE, 11,1)
			RETURN
		END
	END

	SELECT 
		@TASK_ID = TASK_ID,
		@WORKFLOW_NAME = WORKFLOW_NAME,
		@DELETED = DELETED, 
		@TASK_DESC = TASK_DESC,
		@TASK_GRP_ID = TASK_GRP_ID,
		@CMD_PARAMETERS = CMD_PARAMETERS,
		@NO_OF_RETRY = NO_OF_RETRY,
		@SLEEP_BETWEEN_RETRY = SLEEP_BETWEEN_RETRY,
		@TIMEOUT = [TIMEOUT],
		@MACHINE_NAME = MACHINE_NAME
	FROM 
		CHO_TASKS 
	WHERE 
		TASK_NAME = @TASK_NAME

	IF NOT (@NEW_TASK_GRP_NAME IS NULL)
		SELECT @TASK_GRP_ID = @NEW_TASK_GRP_ID

	IF @DELETED = 1
	BEGIN
		SET @ERR_MESSAGE = @TASK_NAME + ' task is in deleted state.'
		RAISERROR (@ERR_MESSAGE, 11,1)
		RETURN
	END
	ELSE
	BEGIN
		-- CLONE TASK 
		EXEC CHO_ADD_OR_UPDATE_TASK @NEW_TASK_NAME, @WORKFLOW_NAME, @TASK_DESC, @TASK_GRP_ID, @CMD_PARAMETERS,
			@NO_OF_RETRY, @SLEEP_BETWEEN_RETRY, @TIMEOUT, @MACHINE_NAME, @USER_NAME, @NEW_TASK_ID OUT

		-- CLONE TASK SCHEDULES
		EXEC CHO_CLONE_TASK_SCHEDULE_DEFINITION @TASK_ID, @NEW_TASK_ID, @USER_NAME
	END
GO

------------------------------------------------------------------------------

DECLARE @TASK_ID INT
EXECUTE CHO_ADD_OR_UPDATE_TASK 'CHUBB_EOD1', 'CHUBB_EOD1', 'CHUBB EOD Jobs', 1, NULL, NULL, NULL, NULL, 'D076WCS004021NN', 'Raj', @TASK_ID OUT
SELECT "@TASK_ID" = @TASK_ID
GO

DECLARE @TASK_ID INT
EXECUTE CHO_ADD_OR_UPDATE_TASK 'MM_SOD1', 'CHUBB_EOD1', 'Mass Mutual SOD Jobs', 2, NULL, NULL, NULL, NULL, 'D076WCS004021NN',  'Raj', @TASK_ID OUT
SELECT "@TASK_ID" = @TASK_ID
GO
